package com.hingstd.lastwork.dao;

import com.hingstd.lastwork.dto.UserAccountArticle;
import com.hingstd.lastwork.entities.UserAccount;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.CrudRepository;

import java.util.List;

public interface UserAccountRepository extends CrudRepository<UserAccount, Long> {
    @Override
    List<UserAccount> findAll();

    @SuppressWarnings("SqlResolve")
    @Query(nativeQuery = true, value ="SELECT s.username, COUNT(s.user_id) AS articleCount " +
            "FROM user_accounts AS s LEFT JOIN article_user_accounts AS ps ON s.user_id = ps.user_id " +
            "GROUP BY s.username ORDER BY 2 desc;")
    List<UserAccountArticle> userAccountArticles();
}
